﻿<?xml version="1.0" encoding="utf-8" ?>
<AdoNetDataAccess connection="$ArticleDBConnection$" formatter="bizobjlist of Article(primaryPicture)">
	<FormatterInfo>
		<code for="vars" on="Article">
			int firstDepth = -1;
			bool resetDepth = false;
			bool stripLeading = true;
			bool setup = false;
		</code>
		<code for="postEach" on="Article">
			<![CDATA[
			try
			{
        if(!setup)
        {
          if(info.get("stripLeading") is bool)
            stripLeading = (bool)info.get("stripLeading");
          else if(info.get("stripLeading") is string)
            stripLeading = (string.Compare((string)info.get("stripLeading"), "False", true) == 0)?false:true;
          resetDepth = info.get("resetDepth") is string && string.Compare((string)info.get("resetDepth"), "true", true) == 0;
          setup = true;
        }
          
				if(CurrentBizObj.get("mediaCacheName").ToString().Length==0)
					CurrentBizObj.put("mediaCacheName",info.get("defaultCacheName"));
        if(stripLeading)
        {
          string title = (string)CurrentBizObj.get("baseTitle");
          int titIndex = title.IndexOf("~!");
          if(titIndex != -1)
            CurrentBizObj.put("baseTitle", title.Substring(titIndex+2));
        }
			}
			catch(Exception e)
			{
			}
			 
			try
			{
				//The problem with the following 3 lines is that when we chunk the articles
				//we dont know what the real start dept is.  We need this to be a parameter into
				//the call so we know what the true start depth really is.
        if(resetDepth)
        {
				  if(firstDepth == -1)
					  firstDepth = (int)CurrentBizObj.get("depth");
				  CurrentBizObj.put("depth", (int)CurrentBizObj.get("depth") - firstDepth);						
        }
			}
			catch(Exception e)
			{
			
			}
			
		]]>
		</code>
		<code for="bizObjType" on="Article">
			<![CDATA[
			obj = App.bizObjMgr.create(info.get("BizObjType") != null?info.get("BizObjType").ToString():"Article");
		]]>
		</code>
		<code for="bizObjListType" on="Article">
			<![CDATA[
			m_list = App.bizObjMgr.createList(info.get("BizObjType") != null?info.get("BizObjType").ToString():"Article");
		]]>
		</code>
	</FormatterInfo>
	
	<Sql>
		<code>DetailLevel details = (DetailLevel)info.get("listDetailLevel");</code>
		Select 
		<code>
			<![CDATA[
				int maxEl = -1;
				int.TryParse((string)info.get("maxElements"), out maxEl);
				if(maxEl > 0)
					sql.Append("top ").Append(maxEl);
				StringBuilder columns = new StringBuilder(1024);
				columns.Append("a.id,a.isSystem,");

				if(details > DetailLevel.idOnly)
					columns.Append("a.baseTitle,a.uniqueTitle,a.readMoreJump,");
				if(details > DetailLevel.idAndTitle)
				{
					columns.Append(@"
						a.summary AS baseSummary, a.keyword, a.publishAuthorName, a.publishDate, nc.dateUpdated AS counts_dateUpdated, nc.dateCreated AS counts_dateCreated, a.metaType as suggestedDisplayType,
						u.lastName AS counts_author_lastName, u.id AS counts_author_id, u.firstName AS counts_author_firstName, u.userName AS counts_author_userName, 
						u.dateCreated AS counts_author_dateCreated, a.id AS primaryPictureId,
						a.primaryMediaName AS primaryPictureName, a.primaryMediaName AS primaryPictureFileName, a.caption AS primaryPictureCaption, 
					");
					if(Cmn.IsTrue(info.get("includeTranslations"))) 
						columns.Append("a.translatedTitles,a.translatedSummaries,a.translationDetails,a.translatedCaptions,");
				}
				if(details > DetailLevel.basicDetails)
				{
					columns.Append("a.body AS baseBody,");
					if(true.Equals(info.get("includeTranslations")))
						columns.Append("a.translatedBodies,");
				}
			]]>
		</code>
		<code>
			<![CDATA[
			if(Cmn.IsTrue(info.get("includeECom")))
				columns.Append(@"
					ec.partId,ec.supplierPartId,ec.supplierId,
					ec.avgCostPrice,ec.listPrice,ec.sellPrice,ec.sellPriceDescription,
					ec.weight AS weightamount,weightUnit,
					ec.width AS [dimensions_width_amount],ec.widthUnit AS [dimensions_width_unit],
					ec.length AS [dimensions_length_amount],ec.lengthUnit AS [dimensions_length_unit],
					ec.depth AS [dimensions_depth_amount],ec.depthUnit AS [dimensions_depth_unit],
					ec.pricingId,");
			if(Cmn.IsTrue(info.get("includeCounts")))
				columns.Append(@"nc.totalL1 AS counts_totalL1, nc.totalL2 AS counts_totalL2, nc.totalL3 AS counts_totalL3,
					nc.totalL4 AS counts_totalL4, nc.totalViews AS counts_totalViews, nc.lastViewed AS counts_lastViewed,
					nc.lastChildUpdated AS counts_lastChildUpdated, lu.userName AS counts_lastChildUpdatedBy_userName, ");
					
			columns.Append("t.objectId, t.parentId, t.parentHostId, nc.keyName, nc.depth, nc.rootId, nc.treeName ");
			sql.Append(columns);
		]]>
		</code>
		FROM
		Articles AS a
		LEFT OUTER JOIN	ObjectTreeNodes AS t ON a.id = t.objectId
		LEFT OUTER JOIN ObjectTreeCounts as nc on nc.objectId = a.id
		<code>
			<![CDATA[
			if(Cmn.IsTrue(info.get("includeCounts")))
				sql.Append(@" LEFT OUTER JOIN Users AS lu ON lu.id = nc.lastChildUpdatedByUserId ");
			if(details > DetailLevel.idAndTitle)
				sql.Append(@"
					LEFT OUTER JOIN	Users AS u ON u.id = nc.authorId 
				");
			]]>
		</code>
		<code>
			<![CDATA[
		if(true.Equals(info.get("includeECom")))
			sql.Append(@"LEFT OUTER JOIN eComProducts AS ec ON ec.id = a.id ");
		]]>
		</code>
		WHERE
		<code>
			<![CDATA[
			bool addAnd = true;
			bool entireTree = Convert.ToBoolean(info.get("entireTree"));
			bool includeSelf = Convert.ToBoolean(info.get("includeSelf"));
			bool parentIdProvided = info.get("parentId") != null;
			if(parentIdProvided)
			{
				if(entireTree && includeSelf)
					sql.Append("t.parentHostId = @parentId");	
				else if(entireTree && !includeSelf)
					sql.Append("(t.parentHostId = @parentId AND t.id <> @parentId)");
				else if(!entireTree && includeSelf)
					sql.Append("(t.parentId = @parentId OR t.id = @parentId)");
				else if(!entireTree && !includeSelf)
					sql.Append("t.parentId = @parentId AND t.id <> @parentId");
			}
			else if(!entireTree)
				sql.Append("t.id = nc.rootId");
			else
				addAnd = false;
			if(addAnd)
				sql.Append(" AND ");
			]]>
		</code>

		<code>
			<![CDATA[
				int startDepth = info.get("startDepth") is int?(int)info.get("startDepth"):-1;
				int endDepth = info.get("endDepth") is int?(int)info.get("endDepth"):-1;
				if(startDepth != -1 && startDepth == endDepth)
					sql.Append("nc.depth = ").Append(startDepth).Append(" AND ");
				else if(startDepth != -1 && endDepth != -1)
					sql.AppendFormat("nc.depth >= {0} AND nc.depth <= {1} AND ", startDepth, endDepth);
			]]>
		</code>
		<code>
			<![CDATA[
			bool hasCriteria = false;
			string firstPerc = "", secondPerc = "", cx="x";
			if(criteria != null && ((IBizObjList)criteria.get("filterOptions")).numElements != 0)
			{
				sql.Append("(");
				foreach(IBizObj filOp in criteria.get("filterOptions") as IBizObjList)
				{
					cx = filOp.get("criteria") as string;
					if (string.Compare(cx, "s", true) == 0)
						firstPerc = "%";
					else if (string.Compare(cx, "e", true) == 0)
						secondPerc = "%";
					else if (string.Compare(cx, "c", true) == 0)
						firstPerc = secondPerc = "%";
					if (firstPerc.Length > 0 || secondPerc.Length > 0)
						cx = "like";
					if(filOp.get("field") != null && (string)filOp.get("field") != "")
					{
						hasCriteria = true;
						sql.AppendFormat(" {0} {1} '{3}{2}{4}'", filOp.get("field"), cx, filOp.get("value"), firstPerc, secondPerc);
						sql.Append(criteria.get("filterIsAnd").Equals(true)?" AND":"  OR");
					}
				}
				if(hasCriteria)
				{
					sql.Length -= 4;
					sql.Append(") AND ");
				}
				else
					sql.Length -= 1;
			}
		]]>
		</code>

		a.companyId = @companyId
		<code>
			<![CDATA[
				if(info.get("objectTreeName") is string)
				{
					sql.Append(" AND nc.treeName = @treeName ");
					addParameter(cmd,"objectTreeName","treeName","VarChar", info);
				}
				if(!parentIdProvided || !entireTree)
					sql.Append(" AND t.objectId = t.parentHostId ");
			]]>
		</code>
		
		<code>
			<![CDATA[
				string orderBy = "ORDER BY nc.keyName ";
				string orderByStr = (string)info.get("orderBy");
				if(Cmn.IsEmpty(orderByStr) && criteria != null && ((IBizObjList)criteria.get("sortOptions")).numElements != 0)
				{
					StringBuilder sortInfo = new StringBuilder(512);
					foreach(IBizObj sortOp in criteria.get("sortOptions") as IBizObjList)
					{
						if(sortInfo.Length > 0)
							sortInfo.Append(",");
						sortInfo.Append(sortOp.get("field"));
						if(Cmn.IsFalse(sortOp.get("isAsc")))
							sortInfo.Append(" DESC");
					}
					orderByStr = sortInfo.ToString();
				}
				if(orderByStr != null && string.Compare("keyname", orderByStr, true) != 0)
				{
					if(string.Compare("lastChildUpdated", orderByStr, true) == 0)
						orderBy = "ORDER BY nc.lastChildUpdated ";
					else
						orderBy = "ORDER BY " + orderByStr + " ";
				}
				if(info.get("sortDir")!= null)
					orderBy += info.get("sortDir").ToString();
				sql.Append(orderBy);
			]]>
		</code>;
	</Sql>
	<Param name="parentId" type="UniqueIdentifier" />
	<Param name="companyId" type="UniqueIdentifier" />
</AdoNetDataAccess>
